﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;

namespace DocPlat.Library
{
    public class ServerObject
    {
        DataBridge mDataBridge = null;
        private DataTable mTableNames;
        private DataTable mTableFieldNames;

        public ServerObject()
        {
            mDataBridge = new DataBridge();

            string commandTableList = @"SELECT " + UFT.Table_TableList.COLUMN_TableID
                                            + ", " + UFT.Table_TableList.COLUMN_TableNameDB
                                            + " FROM " + UFT.Table_TableList.TABLE_NAME;

            string commandFieldList = @"SELECT " + UFT.Table_FieldsList.COLUMN_FieldID
                                            + ", " + UFT.Table_FieldsList.COLUMN_FieldNameDB
                                            + " FROM " + UFT.Table_FieldsList.TABLE_NAME;

            if (mDataBridge.Connect() == true)
            {
                mTableNames = mDataBridge.ExecuteQuery(commandTableList);
                //mLastError = mDataBridge.LastError;

                mTableFieldNames = mDataBridge.ExecuteQuery(commandFieldList);
                //mLastError = mDataBridge.LastError;
                mDataBridge.Disconnect();
            }      
        }
        
        private string GetTableName(int tableID)
        {
            string tableName = string.Empty;
            if (mTableNames != null && mTableNames.Rows.Count > 0)
            {
                string expression = UFT.Table_TableList.COLUMN_TableID + " = " + tableID;
                DataRow[] selectedRows = mTableNames.Select(expression);
                if (selectedRows != null && selectedRows.Length == 1)
                {
                    tableName = (string)selectedRows[0].ItemArray[1];
                }
                else
                {
                    throw new Exception("Multiple values for one table name code!");
                }

            }
            return tableName;
        }

        private string GetColumnName(int columnID)
        {
            string columnName = string.Empty;
            if (mTableFieldNames != null && mTableFieldNames.Rows.Count > 0)
            {
                string expression = UFT.Table_FieldsList.COLUMN_FieldID + " = " + columnID;
                DataRow[] selectedRows = mTableFieldNames.Select(expression);
                if (selectedRows != null && selectedRows.Length == 1)
                {
                    columnName = (string)selectedRows[0].ItemArray[1];
                }
                else
                {
                    throw new Exception("Multiple values for one table name code!");
                }

            }
            return columnName;
        }

        public bool AutentificateUser(string username, string password)
        {
            bool result = false;
            DataTable returnTable = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT UserID "
                                            + " FROM USERS "
                                            + " WHERE Login ='" + username + "' "
                                            + " AND Password ='" + password + "' ";

                    returnTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            if (returnTable != null && returnTable.Rows.Count > 0) result = true;

            return result;
        }

        public void CloseThisTab(UserControl MyPpage)
        {
            TabPage tabpage = (TabPage)MyPpage.Parent;
            TabControl tabControl = (TabControl)tabpage.Parent;

            tabControl.TabPages.Remove(tabpage);
        }


        #region CompanyList

        public DataTable GetCompanyList(bool onlyActive)
        {
            DataTable resultTable = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT                                                                                        
                                             Company.Name 
                                            , Company.Short_name                                           
                                            , Company.Address
                                            , Company.CodFiscal
                                            , Company.isDeleted

                                            FROM Company ";
                    if (onlyActive) commandText += " Where  Company.isDeleted = False";
                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return resultTable;
        }

        public DataTable GetCompanyByID(string CodFiscal, bool onlyActive)
        {
            DataTable resultTable = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT                                                                                        
                                             Company.Name 
                                            , Company.Short_name                                           
                                            , Company.Address
                                            , Company.CodFiscal
                                            , Company.isDeleted

                                            FROM Company 
                                            Where CodFiscal = '" + CodFiscal + "' ";
                    if (onlyActive) commandText += " AND  Company.isDeleted = False";
                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return resultTable;
        }

        public bool AddCompany(string companyName, string shortName, string address, string codFiscal, bool isDeleted)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Company ([Name], Short_name, Address, CodFiscal, isDeleted) "
                        + "VALUES ( "
                        + " '" + companyName + "' "
                        + ", '" + shortName + "' "
                        + ", '" + address + "' "
                        + ", '" + codFiscal + "' "
                        + ", " + isDeleted + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateCompany(string companyName, string shortName, string address, string codFiscal, bool isDeleted)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"UPDATE Company SET "
                      + " [Name] = '" + companyName + "' "
                      + " , Short_name = '" + shortName + "' "
                      + " , Address = '" + address + "' "
                      + " , isDeleted = " + isDeleted + " "
                      + " Where CodFiscal = '" + codFiscal + "' ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool DeleteCompany(string CodFiscal)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"UPDATE Company SET "
                      + " isDeleted = True "
                      + " Where CodFiscal = '" + CodFiscal + "' ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
 
        }

        #endregion CompanyList

        #region ClassifierType

        public bool AddClassifierType(string name, bool isSystemClass)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO ClassifierTypes (Name, isDelete, isSystemClassifier) "
                        + "VALUES ( "
                        + " '" + name + "' "
                        + ", False "
                        + ", " + isSystemClass + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateClassifierType(int typeID, string nameType, bool isSystemClass)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE ClassifierTypes "
                            + "SET ClassifierTypes.[Name] = '" + nameType + "' "
                            + ",  ClassifierTypes.[isSystemClassifier] = " + isSystemClass + " "
                            + " WHERE ClassifierTypes.TypeID = " + typeID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public DataTable SelectTypeClassifiers(bool isDelete, bool isSystemClassifier)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT TypeID "
                        + ", Name "
                        + " FROM ClassifierTypes "
                        + " WHERE isDelete = " + Convert.ToInt32(isDelete) + " "
                        + " AND isSystemClassifier = " + isSystemClassifier + " "
                        + " ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        #endregion ClassifierType

        #region Classifiers

        public bool AddClassifiers(int typeID, string name, string description)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Classifiers (TypeID, [Name], Description, isDelete) "
                        + "VALUES ( "
                        + typeID
                        + ", '" + name + "' "
                        + ", '" + description + "' "
                        + ", False "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateClassifiers(int codeID, int typeID, string nameClassifiers, string description)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE Classifiers "
                            + "SET Classifiers.[TypeID] = " + typeID + " "
                            + ", Classifiers.[Name] = '" + nameClassifiers + "' "
                            + ", Classifiers.[Description] ='" + description + "' "
                            + " WHERE Classifiers.CodeID = " + codeID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public DataTable GetClassifiersListByTypeID(int typeID)
        {
            DataTable classifierTypesByIDList = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_Code) + "] "
                                         + ", Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_Name) + "] "
                                         + ", Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_Description) + "] "
                                         + " FROM " + GetTableName(UFT.Table_Classifiers.TABLE_NAME) + " as Classifiers "
                                        + " WHERE Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_TypeID) + "] = " + typeID + " ";
                    commandText += " ORDER BY Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_Name) + "] asc ";
                    classifierTypesByIDList = mDataBridge.ExecuteQuery(commandText);
                    // mLastError = mDataBridge.LastError;
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return classifierTypesByIDList;
        }

        public DataTable SelectClassifiers(int typeID, bool isDelete, bool isSystemClassifier)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT Classifiers.CodeID "
                        + ", Classifiers.Name "
                        + ", Classifiers.Description "
                        + ", ClassifierTypes.Name AS Grupe "
                        + " FROM Classifiers INNER JOIN ClassifierTypes "
                        + " ON Classifiers.TypeID = ClassifierTypes.TypeID "
                        + " WHERE Classifiers.isDelete = " + Convert.ToInt32(isDelete) + " "
                        + " ";
                    if (typeID > 1)
                    {
                        commandText += " AND Classifiers.TypeID = " + typeID;
                        commandText += " AND ClassifierTypes.isSystemClassifier = " + isSystemClassifier;
                    }

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        #endregion Classifiers       

        #region Users

        public DataTable GetUserList()
        {
            DataTable result = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT 
                                            Users.UserID, 
                                            Users.FirstName, 
                                            Users.LastName, 
                                            Users.Login, 
                                            Users.Password, 
                                            Users.isDelete, 
                                            Users.RoleType,
                                            Users.PasswordStatus,
                                            Classifiers.Name  as [Role Type], 
                                            Classifiers_1.Name as [Password Status]
                                            FROM Classifiers AS Classifiers_1 INNER JOIN 
                                                (Classifiers INNER JOIN Users 
                                                    ON Classifiers.CodeID = Users.RoleType) 
                                                ON Classifiers_1.CodeID = Users.PasswordStatus; ";
                    result = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool AddUser(string FirstName, string LastName, string Login, string Password, bool isDelete, int RoleType, int PasswordStatus)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Users ([FirstName], [LastName], [Login], [Password], [isDelete], [RoleType], [PasswordStatus]) "
                        + "VALUES ( "
                        + "  '" + FirstName + "'  "
                        + ", '" + LastName + "' "
                        + ", '" + Login + "' "
                        + ", '" + Password + "' "
                        + ", " + isDelete + " "
                        + ", " + RoleType + " "
                        + ", " + PasswordStatus + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateUser(int UserID, string FirstName, string LastName, string Login, string Password, bool isDelete, int RoleType, int PasswordStatus)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"UPDATE Users "
                            + " SET FirstName = '" + FirstName + "' "
                            + ", LastName = '" + LastName + "' "
                            + ", [Login] = '" + Login + "' "
                            + ", [Password] = '" + Password + "' "
                            + ", [isDelete] = " + isDelete + " "
                            + ", [RoleType] = " + RoleType + " "
                            + ", PasswordStatus = " + PasswordStatus + " "
                            + " WHERE UserID = " + UserID + "; "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public DataTable GetUserByID(int userID)
        {
            DataTable resultTable = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT * From Users WHERE UserID = " + userID + "; ";
                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return resultTable;
        }

        public bool DeleteUser(int userID)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"Delete From Users WHERE UserID = " + userID + "; ";  
                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }


        #endregion Users       

        #region DocPlatHistory

        public bool GetNewNrBonPlat(out int docPlatNr)
        {
            bool result = false;
            docPlatNr = 0;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"Select Max(id) From PayHistory ";
                    DataTable resultTable = mDataBridge.ExecuteQuery(commandText);

                    if (resultTable != null && resultTable.Rows.Count == 1)
                    {
                        int.TryParse(resultTable.Rows[0][0].ToString(), out docPlatNr);

                        string nonQuery = @"INSERT INTO PayHistory (id, edit_date )"
                            + " Values (" + (docPlatNr+1).ToString() + ", '" + DateTime.Now.ToString("yyyy/MM/dd") + "')";

                        result = mDataBridge.ExecuteNonQuery(nonQuery);

                    }
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool CheckExistDocPlat(int idDocplat)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT * FROM  PayHistory Where id = " + idDocplat + "  ;";
                    DataTable tableResult = mDataBridge.ExecuteQuery(commandText);

                    if (tableResult != null && tableResult.Rows.Count == 1)
                    {
                        result = true;
                    }
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }


        public DataTable GetDocPlatByID(int idDocPlat)
        {
            DataTable result = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT 
                                                PayHistory.id, 
                                                PayHistory.date, 
                                                PayHistory.cod_fisc_benef, 
                                                Company.Name as [Company Name], 
                                                Bank.Description as [Banc Description], 
                                                Bank.Cont, 
                                                Bank.id as [ID Banca],
                                                Payer.FirstName, 
                                                Payer.LastName, 
                                                Payer.Patronimic, 
                                                PayHistory.idnoPlatitor, 
                                                Company.Address, 
                                                PayHistory.summaTotala

                                                FROM Company INNER JOIN 
                                                        (Bank INNER JOIN 
                                                                (Payer INNER JOIN PayHistory ON Payer.IDNO = PayHistory.idnoPlatitor) 
                                                            ON Bank.id = PayHistory.idBank) 
                                                    ON Company.CodFiscal = PayHistory.cod_fisc_benef 
                                                
                                                Where PayHistory.id = " + idDocPlat + "  ;";

                    result = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public DataTable GetDocPlatList()
        {
            DataTable result = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT 
                                            PayHistory.id, 
                                            PayHistory.date as [Data], 
                                            Payer.FirstName + ' ' + Payer.LastName as [Platitor],
                                            PayHistory.idnoPlatitor as [IDNO],
                                            PayHistory.summaTotala 
                                           
                                            FROM Payer INNER JOIN PayHistory ON Payer.IDNO = PayHistory.idnoPlatitor;";

                    result = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool AddNewServiceInDocPlat(int idBonPlat, int idService)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" Insert INTO PayHisServ (idDocPlat, idService) "
                            + " Values (" + idBonPlat + ", " + idService + ") ";
                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool DeleteServiceInDocPlat(int idBonPlat, int idService)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                { 
                    string nonQuery = @"Delete  FROM PayHisServ "
                    + " WHERE  (  ((PayHisServ.idDocPlat)=" + idBonPlat + ") AND ((PayHisServ.idService)=" + idService + "  ) ); ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public DataTable GetServiceFromDocPlatList(int idDocPlat)
        {
            DataTable resultTable = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    //SELECT PayHisServ.idDocPlat, PayHisServ.idService, Services.Description, Services.Cost

                    string commandText = @"SELECT
                                            PayHisServ.idDocPlat as [idDocPlat]
                                            , PayHisServ.idService as [idService]
                                            , Services.Description as [Description]
                                            , Services.RegimPrest as [RegimPrest]
                                            , Services.TipulAchit as [TipulAchit]
                                            , Services.Cost as [Cost]

                                            FROM Services INNER JOIN PayHisServ ON Services.id = PayHisServ.idService

                                            Where PayHisServ.idDocPlat = " + idDocPlat + " ;";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return resultTable;
        }

        public bool SaveBonPlat(int idDocPlat, DateTime date, string cod_fisc_benef, string idBank, string idnoPlatitor, double summaTotala)
        {
            bool result = false;
            try
            {
                if (((ServerObject)this).CheckExistDocPlat(idDocPlat))
                {                   
                    if (mDataBridge.Connect() == true)
                    {
                        string nonQuery = @"UPDATE PayHistory SET "                               
                                + " edit_date = '" + DateTime.Now + "' "
                                + ", [date] = '" + date.ToString("dd/MM/yyyy") + "' "
                                + ", cod_fisc_benef = '" + cod_fisc_benef + "' "
                                + ", idBank = " + idBank + " "
                                + ", idnoPlatitor = '" + idnoPlatitor + "' "
                                + ", summaTotala = " + summaTotala + " "
                                + " WHERE id = " + idDocPlat + " ";

                        result = mDataBridge.ExecuteNonQuery(nonQuery);
                    }
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }
        
        #endregion DocPlatHistory

        #region Services

        public DataTable GetServicesList()
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = 
                           @" SELECT id "
                        + " , Description "
                        + " , RegimPrest "
                        + " , TipulAchit "
                        + " , Cost "                       
                        + " FROM Services ";
                   
                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public DataTable GetServicesByID(int idProduct)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText =
                           @" SELECT id "
                        + " , Description "
                        + " , RegimPrest "
                        + " , TipulAchit "
                        + " , Cost "
                        + " FROM Services "
                        + " Where id = " + idProduct;

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public bool AddServices(string description, string regimPrest, string tipulAchit, double cost)
        {
            bool result = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Services (Description, RegimPrest, TipulAchit, Cost) "
                        + "VALUES ( "
                        + " '" + description + "' "
                        + ", '" + regimPrest + "' "
                        + ", '" + tipulAchit + "' "
                        + ", " + cost + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateServices(int idservece, string description, string regimPrest, string tipulAchit, double cost)
        {
            bool result = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"UPDATE Services SET "
                        + " Description = '" + description + "' "
                        + ", RegimPrest = '" + regimPrest + "' "
                        + ", TipulAchit = '" + tipulAchit + "' "
                        + ", Cost = " + cost + " "
                        + " WHERE id = " + idservece + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool DeleteServices(int idService)
        {
            bool result = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"Delete FROM Services Where id = " + idService + " ";                       

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool GetServicesListForPrint(int idDocPlat)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string nonQuery = @"Delete From  ServicesListForPrint";  
                    bool deleteResult = mDataBridge.ExecuteNonQuery(nonQuery);


                    string commandTextInsert = @"INSERT INTO ServicesListForPrint (NrDo, TipService, RegimPrest, TipAchitarii, Suma) "
                                + " SELECT "
                                + " '' as [id], "
                                + " Services.Description, "
                                + " Services.RegimPrest, "
                                + " Services.TipulAchit, "
                                + " Services.Cost "
                                + " FROM Services INNER JOIN PayHisServ ON Services.id = PayHisServ.idService "
                                + " Where PayHisServ.idDocPlat = " + idDocPlat + "; ";

                    result = mDataBridge.ExecuteNonQuery(commandTextInsert);                    
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }          
            
            return result;
        }

        #endregion Services

        #region Bank

        public DataTable GetBankList()
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText =
                           @" SELECT id "
                        + " , Description "
                        + " , Cont "
                        + " , isDefault "
                        + " FROM Bank ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public DataTable GetBankByID(int idBank)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText =
                           @" SELECT id "
                        + " , Description "
                        + " , Cont "
                        + " , isDefault "
                        + " FROM Bank "
                    +" Where id = " + idBank;

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public bool AddBank(string description, string cont, bool isDefault)
        {
            bool result = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Bank (Description, Cont, isDefault) "
                        + "VALUES ( "
                        + " '" + description + "' "
                        + ", '" + cont + "' "
                        + ", " + isDefault + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateBank(int idBank, string description, string cont, bool isDefault)
        {
            bool result = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"UPDATE Bank SET "
                        + " Description = '" + description + "' "
                        + ", cont = '" + cont + "' "
                        + ", isDefault = " + isDefault + " "
                        + " WHERE id = " + idBank + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool DeleteBank(int idBank)
        {
            bool result = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"Delete FROM Bank Where id = " + idBank + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        #endregion Bank
        
        #region Payer

        public DataTable GetPayerList()
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText =
                           @" SELECT IDNO "
                        + " , FirstName "
                        + " , LastName "
                        + " , Patronimic "
                        + " FROM Payer ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public DataTable GetPayerByID(string idno)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText =
                          @" SELECT IDNO "
                        + " , FirstName "
                        + " , LastName "
                        + " , Patronimic "
                        + " FROM Payer "
                        + " Where IDNO = '" + idno + "' ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public bool AddPayer(string idno, string FirstName, string LastName, string Patronimic)
        {
            bool result = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Payer (IDNO, FirstName, LastName, Patronimic) "
                        + "VALUES ( "
                        + " '" + idno + "' "
                        + ", '" + FirstName + "' "
                        + ", '" + LastName + "' "
                        + ", '" + Patronimic + "' "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdatePayer(string idno, string FirstName, string LastName, string Patronimic, string old_IDNO)
        {
            bool result = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"UPDATE Payer SET "
                        + " IDNO = '" + idno + "' "
                        + ", FirstName = '" + FirstName + "' "
                        + ", LastName = '" + LastName + "' "
                        + ", Patronimic = '" + Patronimic + "' "
                        + " WHERE IDNO = '" + old_IDNO + "' ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool DeletePayer(string idno)
        {
            bool result = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"Delete FROM Payer Where IDNO = '" + idno + "' ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        #endregion Payer        
    }
}
